﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace hdbankwebservice.DAO
{
    public class TheTinDungDAO
    {
        //AND thêm điều kiện trạng thái thẻ
        public static TheTinDung LayThongTinThe(string creditCardID)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT *
                                FROM KHACHHANG INNER JOIN
                                                THETINDUNG ON KHACHHANG.MAKHACHHANG = THETINDUNG.MAKHACHHANG
                                WHERE (THETINDUNG.SOTHE = @SoThe)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "SoThe";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;

            // ma hoa so the xuong csdl
            para.Value = MineUtils.ByteArrayToString(MineUtils.MaHoa(creditCardID));
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                TheTinDung theTinDung = new TheTinDung();
                 theTinDung.SoThe = dt.Rows[0]["SOTHE"].ToString();
                // giai ma so the tu csdl
                theTinDung.SoThe = MineUtils.GiaiMa(MineUtils.StringToByteArray(theTinDung.SoThe));

                theTinDung.NgayNhanThe = (DateTime)dt.Rows[0]["NGAYNHANTHE"];
                theTinDung.NgayHetHanThe = (DateTime)dt.Rows[0]["NGAYHETHANTHE"];
                theTinDung.SoBiMat = dt.Rows[0]["SOBIMAT"].ToString();
                theTinDung.MaNhomThe = (int)dt.Rows[0]["MANHOMTHE"];
                theTinDung.MaTrangThaiThe = (int)dt.Rows[0]["MATRANGTHAITHE"];
                theTinDung.KhachHang = new DTO.KhachHang();
                theTinDung.KhachHang.HoTen = dt.Rows[0]["HOTEN"].ToString();
                theTinDung.KhachHang.MaKhachHang = dt.Rows[0]["MAKHACHHANG"].ToString();
                theTinDung.KhachHang.SoTaiKhoan = dt.Rows[0]["SOTAIKHOAN"].ToString();
                //THÊM...
                return theTinDung;
            }
        }
        public static string LaySoTheTinDung(string makhachhang)
        {
            //kiem tra ting trang the
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT *
                                FROM THETINDUNG 
                                WHERE (THETINDUNG.MAKHACHHANG = @makhachhang)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "makhachhang";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = makhachhang;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return "";
            }

           // return dt.Rows[0]["SOTHE"].ToString();
            return MineUtils.GiaiMa(MineUtils.StringToByteArray(dt.Rows[0]["SOTHE"].ToString()));
        }

        public static DataTable LayThongTinChiTiet(string sothe)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"select k.HOTEN,nt.TENNHOMTHE , td.SOTHE,td.SOBIMAT,td.NGAYNHANTHE,td.NGAYHETHANTHE,tt.TENDIENGIAI
                                 from KHACHHANG k, NHOMTHE nt,THETINDUNG td,TRANGTHAITHE tt
                                 where k.MAKHACHHANG=td.MAKHACHHANG and td.MANHOMTHE = nt.MANHOMTHE and tt.MATRANGTHAITHE = td.MATRANGTHAITHE
		                                and td.SOTHE = '" + MineUtils.ByteArrayToString(MineUtils.MaHoa(sothe)) +"'";
            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return dt;
        }
    }
}